﻿Public Class CrashStatus

    Private Sub CrashStatus_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

    Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click

        Dim dt As DataTable = GetData()
        If dt.Rows.Count = 0 Then
            ShowMessageBox("没有数据")
            Return
        End If

        Try

            Dim englishFolder As String = System.IO.Path.Combine(GetCurrentDirectory, "Report")
            If Not System.IO.Directory.Exists(englishFolder) Then
                System.IO.Directory.CreateDirectory(englishFolder)
            End If

            Dim englishPath As String = System.IO.Path.Combine(englishFolder, "CrashStatus.xls")
            Dim bCreate As Boolean = True
            If System.IO.File.Exists(englishPath) Then
                System.IO.File.Delete(englishPath)
            End If

            Dim englishCreate As New CrashStatusC(englishPath)
            englishCreate.报表记录条数字 = Me.报表记录条数字
            englishCreate.报表字体 = Me.报表字体

            AddHandler englishCreate.PrintProcess, AddressOf PrintProcess

            englishCreate.DoOutData(dt)
            RemoveHandler englishCreate.PrintProcess, AddressOf PrintProcess

            '打开文件
            Using pro As New Process
                pro.StartInfo.FileName = englishPath
                pro.Start()
            End Using

        Catch ex As Exception
            ShowMessageBox(ex.ToString)
        End Try

    End Sub

    Private Sub PrintProcess(ByVal content As String)
        lbl1000.Text = content
        System.Windows.Forms.Application.DoEvents()
    End Sub

    Private Function GetData() As DataTable

        Dim dt As New DataTable
        dt.Columns.Add("标题")
        dt.Columns.Add("金额")
        dt.Columns.Add("备注")

        Dim 余额 As Decimal = 0

        dt.Rows.Add(New String() {"帐户名称", "余额", "备注"})
        Dim dtNew As DataTable = Get帐户一览()
        For index As Integer = 0 To dtNew.Rows.Count - 1
            dt.Rows.Add(New Object() {dtNew.Rows(index)(0), dtNew.Rows(index)(1), dtNew.Rows(index)(2)})
            余额 += CDec(dtNew.Rows(index)("金额"))
        Next

        If dtNew.Rows.Count > 0 Then
            dt.Rows.Add(New String() {"借钱人(别人借钱)", "余额", "备注"})
            dtNew = Get借出一览()
            For index As Integer = 0 To dtNew.Rows.Count - 1
                dt.Rows.Add(New Object() {dtNew.Rows(index)(0), dtNew.Rows(index)(1), dtNew.Rows(index)(2)})
                余额 += CDec(dtNew.Rows(index)("金额"))
            Next
        End If

        dtNew = Get未还一览()
        If dtNew.Rows.Count > 0 Then
            dt.Rows.Add(New String() {"借钱人(借别人)", "余额", "备注"})
            For index As Integer = 0 To dtNew.Rows.Count - 1
                dt.Rows.Add(New Object() {dtNew.Rows(index)(0), dtNew.Rows(index)(1), dtNew.Rows(index)(2)})
                余额 += CDec(dtNew.Rows(index)("金额"))
            Next
            余额 += CDec(dtNew.Compute("SUM(金额)", ""))
        End If

        dt.Rows.Add(New Object() {"总合计：", 余额, ""})

        Return dt

    End Function

    Private Function Get帐户一览() As DataTable

        Return ct.FillTable("SELECT title as '标题',count as '金额',Memo as '备注' from CrashStatus")

    End Function

    Private Function Get借出一览() As DataTable

        Dim sb As New System.Text.StringBuilder
        sb.AppendLine("select WysInputUser.UserName as '标题' ")
        sb.AppendLine(",count as '金额',WysBorrowLend.Memo as '备注' ")
        sb.AppendLine("from WysBorrowLend")
        sb.AppendLine("LEFT JOIN WysInputUser")
        sb.AppendLine("on LendBorrowUserName=WysInputUser.ID")
        sb.AppendLine("where WysBorrowLend.Type=0 and WysBorrowLend.Status=0")

        sb.AppendLine("UNION ALL")

        sb.AppendLine("Select WysInputUser.UserName AS '标题'")
        sb.AppendLine(",ifnull((w1.Count-sum(w2.Count)),0) as 金额")
        sb.AppendLine(",w1.Memo as '备注'")
        sb.AppendLine("FROM  WysBorrowLend w1")
        sb.AppendLine("LEFT JOIN WysInputUser")
        sb.AppendLine("ON w1.LendBorrowUserName=WysInputUser.ID")
        sb.AppendLine("LEFT JOIN WysBorrowLend w2")
        sb.AppendLine("On w1.id=w2.LendBorrowUserName")
        sb.AppendLine("WHERE w1.Status=2 and w2.Type=1")

        Return ct.FillTable(sb.ToString())

    End Function

    Private Function Get未还一览() As DataTable

        Dim sb As New System.Text.StringBuilder
        sb.AppendLine("select WysInputUser.UserName as '标题' ")
        sb.AppendLine(",count as '金额',WysBorrowLend.Memo as '备注' ")
        sb.AppendLine("from WysBorrowLend")
        sb.AppendLine("LEFT JOIN WysInputUser")
        sb.AppendLine("on LendBorrowUserName=WysInputUser.ID")
        sb.AppendLine("where WysBorrowLend.Type=2 and WysBorrowLend.Status=0")

        Return ct.FillTable(sb.ToString())

    End Function
End Class
